<?php
/* 
 * Author: Quan Van Sinh
 * Email: sinhvnb@vietnambiz.com
 */
function lastestArticle($limit=20) {
    global $DB;
    $sql = "SELECT title,link,image,short_description FROM article WHERE status='yes' ORDER BY create_date DESC LIMIT $limit";
    return $DB->query($sql);
}


function getLastestArticlesByCategoryId($categoryId) {
    global $DB;
    $arrCatId = get_cat_id_to_arr($categoryId);
    $str_cat_id = '';
    for($i=0;$i<count($arrCatId);$i++) {
        if($i==(count($arrCatId)-1)) {
            $str_cat_id .= $arrCatId[$i];
        }else {
            $str_cat_id .= $arrCatId[$i].',';
        }
    }
    if($str_cat_id=='') {
        $sql= "SELECT a.title,a.image,a.link,a.short_description,c.name as cat_name, c.link as cat_link FROM article a LEFT JOIN article_category c
            ON a.cat_id = c.id WHERE a.cat_id=".intval($categoryId)." AND a.status='yes' ORDER BY a.create_date DESC";
    }else {
        $sql= "SELECT a.title,a.image,a.link,a.short_description,c.name as cat_name, c.link as cat_link FROM article a LEFT JOIN article_category c
            ON a.cat_id = c.id WHERE a.cat_id=".intval($categoryId)." OR a.cat_id IN ($str_cat_id) AND a.status='yes' ORDER BY a.create_date DESC";
    }

    $rs = $DB->query_first($sql);
    return $rs;
}

function get_cat_id_to_arr($parent_id = 0) {
    global $DB;
    if(!$trees) $trees = array();
    $rsCat = $DB->query("SELECT * FROM article_category WHERE  parent_id = ".intval($parent_id));
    if($rsCat) {
        while($rs = $DB->fetch_array($rsCat)) {
            $trees[] = $rs['id'];
        }
    }
    return $trees;
}


function getArticleByCategoryIdOffsetLimit($categoryId,$offset=0,$limit=10) {
    global $DB;
    $arrCatId = get_cat_id_to_arr($categoryId);
    $str_cat_id = '';
    for($i=0;$i<count($arrCatId);$i++) {
        if($i==(count($arrCatId)-1)) {
            $str_cat_id .= $arrCatId[$i];
        }else {
            $str_cat_id .= $arrCatId[$i].',';
        }
    }
    if($str_cat_id == '') {
        $sql= "SELECT id,title,link FROM article WHERE cat_id=".intval($categoryId)." AND status='yes' ORDER BY create_date DESC LIMIT $offset,$limit";
    }else {
        $sql= "SELECT id,title,link FROM article WHERE cat_id=".intval($categoryId)." OR cat_id IN ($str_cat_id) AND status='yes' ORDER BY create_date DESC LIMIT $offset,$limit";
    }

    $rs = $DB->query($sql);
    return $rs;
}
function getArticlesByTag() {
    global $DB, $config;
    $tags_link = filter($config['link']);

    $rsTagId = $DB->query_first("SELECT id,tags FROM tags WHERE tags_link='$tags_link'");
    if($rsTagId) {
        $sql = "SELECT count(a.id) as numrows FROM article a
            LEFT JOIN article_tags t ON a.id=t.article_id WHERE t.tag_id=".$rsTagId['id'] . " AND a.status='yes'";

        $rsTotalRows =  $DB->query_first($sql);
        $totalRows =  $rsTotalRows['numrows'];
        $totalPages = ceil($totalRows/$config ['total_article_on_category']);
        $start = (($config ['page']*$config ['total_article_on_category'])-$config ['total_article_on_category']);
        $limit = $config ['total_article_on_category'];

        $sql = "SELECT a.title,a.link,a.image,a.short_description,a.create_date FROM article a
            LEFT JOIN article_tags t ON a.id=t.article_id WHERE t.tag_id=".$rsTagId['id']." AND a.status='yes' ORDER BY a.create_date DESC LIMIT $start,$limit";
        $rsArticles = $DB->query($sql);
        return array(
        "tag_name" => $rsTagId['tags'],
        "totalRows"   => $totalRows,
        "rsArticles" => $rsArticles
        );
    }else{
        header("Location: ".$config['domain']);
    }

}

function getOrtherOlderArticleByTag($lastTagDate,$limit=10) {
    global $DB, $config;
    $tags_link = filter($config['link']);
    $rsTagId = $DB->query_first("SELECT id FROM tags WHERE tags_link='$tags_link'");
    $sql = "SELECT a.title,a.link FROM article a
            LEFT JOIN article_tags t ON a.id=t.article_id WHERE t.tag_id=".$rsTagId['id']." AND a.create_date < '$lastTagDate' AND a.status='yes' LIMIT $limit";
    return $DB->query($sql);
}
?>
